package unl.cse.albums;

import java.sql.*;

public class AlbumAdder {

	/**
	 * Adds the necessary record(s) to the database corresponding to the album and
	 * band attributes given. Returns the primary key value of the newly inserted
	 * album record.
	 * 
	 * If any error(s) occurs or invalid data is given, this method throws a
	 * {@link RuntimeException}.
	 * 
	 * @param albumTitle
	 * @param bandName
	 * @param albumYear
	 * @param albumNumber
	 * @return
	 */
	public static int addAlbumToDatabase(String albumTitle, String bandName, String albumYear, String albumNumber) {

		// TODO
		Album a = null;

		Connection conn = null;

		try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection(DatabaseInfo.URL, DatabaseInfo.USERNAME, DatabaseInfo.PASSWORD);
		} catch (SQLException | ClassNotFoundException e) {
			System.out.println("SQLException: conn can not get ");
			e.printStackTrace();
			throw new RuntimeException(e);
		}

		int bandId=0;
		int album_id=0;
		String band_check_sql = "select * from Band where name=?";
		String band_sql = "INSERT INTO Band(`name`)VALUES(?)";
		String sql = "INSERT INTO Album(title,`year`,`number`,bandId) VALUES(?,?,?,?) ";
		PreparedStatement ps = null;
		PreparedStatement band_ps = null;
		PreparedStatement band_check_sql_ps = null;

		ResultSet band_check_sql_rs = null;

		try {
			band_check_sql_ps= conn.prepareStatement(band_check_sql);
			band_check_sql_ps.setString(1,bandName);
			band_check_sql_rs =band_check_sql_ps.executeQuery();
			if(band_check_sql_rs.next()){
				bandId = band_check_sql_rs.getInt("bandId");
			}else {
				band_ps = conn.prepareStatement(band_sql,Statement.RETURN_GENERATED_KEYS);
				band_ps.setString(1,bandName);
				band_ps.executeUpdate();
				ResultSet rs = band_ps.getGeneratedKeys();
				if (rs.next()) {
					bandId = rs.getInt(1);
				}
			}

			ps = conn.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);

			ps.setString(1, albumTitle);
			ps.setInt(2,Integer.valueOf(albumYear));
			ps.setInt(3,Integer.valueOf(albumNumber));
			ps.setInt(4,bandId);
			ps.execute();

			ResultSet rs = ps.getGeneratedKeys();
			if (rs.next()) {
				album_id = rs.getInt(1);
				System.out.println("album_id：" + album_id);
			}

			if(rs != null && !rs.isClosed()){
				rs.close();
			}
			if(band_check_sql_ps != null && !band_check_sql_ps.isClosed()){
				band_check_sql_ps.close();
			}
			if(band_ps != null && !band_ps.isClosed()){
				band_ps.close();
			}
			if(ps != null && !ps.isClosed()){
				ps.close();
			}

		} catch (SQLException e) {
			System.out.println("SQLException: jdbc error ");
			e.printStackTrace();
			throw new RuntimeException(e);
		}


		return album_id;
	}

}
